package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBCustomer implements IFDBCustomer
{
    private Connection db_con;

    public DBCustomer()
    {
        db_con = DBConnection1.getInstance().getDBcon();
    }
    
    private String buildQuery(String wClause)
    {
    	String query="SELECT * FROM Customer";
    	
    	if (wClause.length()>0)
    	{
    		query=query+" WHERE "+ wClause;
    	}
    	return query;
    }
    
    private Customer buildCustomer(ResultSet results)
    {
        Customer custObj = new Customer();
        try 
        {
        	custObj.setID(results.getInt(1));
        	custObj.setName(results.getString(2));
        	custObj.setAddress(results.getString(3));
        	custObj.setzipCode(results.getInt(4));
        	custObj.setPhone(results.getString(5));
        	custObj.setEmail(results.getString(6));
        	custObj.setCity(results.getString(7));
        }
        
        catch(Exception e)
        {
        	System.out.println("error in building the customer object");
        }
        
        return custObj;
    }
    
    private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
    {
    	ResultSet results;
        ArrayList<Customer> custList = new ArrayList<Customer>();
        
        String query =  buildQuery(wClause);
        
        try{
        	Statement stmt = db_con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
        	while( results.next() )
        	{
        		Customer custObj = new Customer();
        		custObj = buildCustomer(results);
        		custList.add(custObj);
        	}
        	stmt.close();
        }
        catch(Exception e)
        {
        	System.out.println("Query exception - select: "+e);
        	e.printStackTrace();
        }
        return custList;
    }
    
    private Customer singleWhere(String wClause, boolean retrieveAssociation)
    {
    	ResultSet results;
    	Customer custObj = new Customer();
    	String query =  buildQuery(wClause);
    	System.out.println(query);
    	try
    	{
    		Statement stmt = db_con.createStatement();
    		stmt.setQueryTimeout(5);
    		results = stmt.executeQuery(query);
    		
    		if( results.next() )
    		{
    			custObj = buildCustomer(results);
    			stmt.close();
    		}
    	}
    	catch(Exception e) 
    	{
    		System.out.println("Query exception: " + e);
    	}
    	return custObj;
    }
    
    //insert new Customer
    public int insertCustomer(Customer cust) throws Exception
    {
    	int result = -1;
    	String query = "INSERT INTO Customer(name, address, zipcode, phone, email, city) VALUES ('" +
    	cust.getName() + "', '" +
    	cust.getAddress() + "', '" +
    	cust.getzipCode() + "', '" + 
    	cust.getPhone() + "', '" + 
    	cust.getEmail() + "', '" +
    	cust.getCity() + " ')";
    	
    	System.out.println("insert: " + query);
    	try
    	{
    		Statement stmt = db_con.createStatement();
    		stmt.setQueryTimeout(5);
    		result = stmt.executeUpdate(query);
    		stmt.close();
    	}
    	catch(SQLException ex)
	    {
	    	System.out.println("Customer has not been created.");
	    	throw new Exception ("Customer has not been inserted correctly.");
	    }
	    return(result);
    }
    
    //update informations for one Customer
    public int updateCustomer(Customer cust)
    {
    	Customer custObj=cust;
		
		String query="UPDATE Customer SET "+
		" name= '" + custObj.getName() + "'," +
		" address= '" + custObj.getAddress() + "'," +
		" zipcode= '" + custObj.getzipCode() + "'," +
		" phone= '" + custObj.getPhone() + "'," +
		" email= '" + custObj.getEmail() + "', " +
		" city= '" + custObj.getCity() + "' " +
		" WHERE custId= '" + custObj.getID() + "'";
		
		int result=-1;
		System.out.println("Update query:" + query);
		
		try
		{ // update supplier
			Statement stmt = db_con.createStatement();		
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exception in Customer db: "+ex);
		}
		return(result);
    }
    
	@Override
	public ArrayList<Customer> getAllCustomers(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Customer getCustomerByName(String name, boolean retrieveAssociation)
	{
		String wClause = "name = '"+ name +"'";
    	return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public Customer getCustomerByID(int id, boolean retrieveAssociation)
	{
		String wClause = "custId = '"+ id +"'";
    	return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int deleteCustomer(int id) 
	{
		int result = -1;

        String query = "DELETE FROM Customer WHERE custId = '" + id + "'";
        System.out.println("delete: " + query);
        
        try
        {
        	Statement stmt = db_con.createStatement();
        	stmt.setQueryTimeout(5);
        	result = stmt.executeUpdate(query);
        	stmt.close();
        }
        
        catch(Exception ex)
        {
                System.out.println("Delete exception in Customers db: "+ex);
        }
        return result;
	}
}